
package com.zoo.transfer

import com.mongodb.util.JSON
import com.mongodb.BasicDBObject

class Convert {

    def customerTable(sql, collect, page, pageSize) {

        def start = System.currentTimeMillis()

        def customer, orders, order_line, custcnt = 0, ordercnt = 0, itemcnt = 0
        sql.eachRow(Customer.GET_CUSTOMER,[page * pageSize, pageSize]) { cust ->
            custcnt++

            customer = new Customer([
                'cid': cust.customer_id,
                'who': cust.type,
                'eml': cust.email,
                'pwd': cust.password,
                'resl': [
                    'code': cust.reseller_code,
                    'accw': cust.accwareAccount,
                    'sacc': cust.shippingAccount,
                    'note': cust.notice
                ],
                'check': [
                    'licn': cust.driversNumber,
                    'stat': cust.driversState,
                    'nmbr': cust.checkNumber,
                    'acnt': cust.accountNumber,
                    'rout': cust.routingNumber
                ],
                'post': [[
                    'tp':   'B',
                    'comp': cust.shippingCompany,
                    'fn':   cust.shippingFirstName,
                    'ln':   cust.shippingLastName,
                    'ad1':  cust.shipping_address1,
                    'ad2':  cust.shipping_address2,
                    'cty':  cust.shipping_city,
                    'stt':  cust.shipping_state,
                    'cnty': cust.shipping_country,
                    'zp':   cust.shipping_zip,
                    'zp4':  cust.shipping_zip4,
                    'ph':   cust.shippingPhone,
                    'cel':  cust.shippingCell,
                    'fx':   cust.shippingFax,
                    'act':  1
                ], [
                    'tp':   'S',
                    'comp': cust.billingCompany,
                    'fn':   cust.billingFirstName,
                    'ln':   cust.billingLastName,
                    'ad1':  cust.billing_address1,
                    'ad2':  cust.billing_address2,
                    'cty':  cust.billing_city,
                    'stt':  cust.billing_state,
                    'cnty': cust.billing_country,
                    'zp':   cust.billing_zip,
                    'zp4':  cust.billing_zip4,
                    'ph':   cust.billingPhone,
                    'cel':  cust.billingCell,
                    'fx':   cust.billingFax,
                    'act':  1
                ]],
                'cards': [[
                    'nm':   cust.cc_name,
                    'cn':   cust.cardNumber,
                    'add':  cust.cc_number_add,
                    'exp':  cust.cc_exp_date,
                    'ph':   cust.bankPhone,
                    'act':  1
                ]],
                'join': cust.joined
            ])

            orders = []
            sql.eachRow(Customer.GET_ORDERS,[cust.customer_id]) { order ->
                ordercnt++

                order_line = []
                sql.eachRow(Customer.GET_ORDER_LINE,[order.order_id]) { line ->
                    itemcnt++

                    order_line << [
                        'qty':  line.quantity,
                        'sprc': line.sell_price,
                        'cprc': line.cost_price,
                        'wty': [
                            'desc': line.warrantyDesc,
                            'prc':  line.warrantyPrice
                        ],
                        'vnd': line.alias
                    ]
                }// line

                orders << [
                    'no':   order.orderNo,
                    'date': order.order_date,
                    'po':   order.customerPO,
                    'slip': order.slip,
                    'tax':  order.tax_price,
                    'pay':  order.paymentMethod,
                    'wty':  order.wty,
                    'amnt': order.amount,
                    'auth': order.auth_status,
                    'imp':  order.imp,
                    'ship': [
                        'prc':  order.shipping_price,
                        'code': order.shipping_code,
                        'sign': order.sign,
                        'blnd': order.blind,
                        'cod':  order.cod
                    ],
                    'adv': [
                        'id':  order.advert_id,
                        'ref': order.advertRefID,
                    ],
                    'trans': [
                        'id':  order.trans_id,
                        'fee': order.trans_fee
                    ],
                    'cj': [
                        'pid': order.cjpid,
                        'aid': order.cjaid,
                        'sid': order.cjsid
                    ],
                    'itms': order_line
                ]
            } //orders
            customer.put('ords', orders)

            //println cust.toString()
            //println cust.who
            collect.insert(customer)
        } // cust

        return [page:page, custcount:custcnt, ordercount:ordercnt, itemcount:itemcnt, time:(System.currentTimeMillis() - start)]
    }

    def customerTableJSON(sql, collect, page, pageSize) {

        def start = System.currentTimeMillis(), email, custcnt = 0, ordercnt = 0, itemcnt = 0, order, line

        sql.eachRow(Customer.GET_CUSTOMER_JSON,[page * pageSize, pageSize]) { cust ->
            custcnt++
            email = cust.email
            try {
                collect.insert(JSON.parse(cust.json) as BasicDBObject)

                sql.eachRow(Customer.GET_ORDERS_JSON,[cust.customer_id]) { ord ->
                    ordercnt++

                    order = JSON.parse(ord.json) as BasicDBObject
                    //println "order=${order}"
                    collect.update([cid: cust.customer_id] as BasicDBObject, [$push: [ords: order]] as BasicDBObject)

                    sql.eachRow(Customer.GET_ORDER_LINE_JSON,[ord.order_id]) { ln ->
                        itemcnt++

                        line = JSON.parse(ln.json) as BasicDBObject
                        //println "cid:${cust.customer_id}, oid:${ord.order_id} line=${line}"
                        collect.update([cid: cust.customer_id, 'ords.oid': ord.order_id] as BasicDBObject, [$push: ['ords.$.itms': line]] as BasicDBObject)

                    } // line
                } // orders
            }
            catch(e) {
                println "id:${cust.customer_id}, email:${email}, error:${e}"
            }
        } // customer

        return [page:page, custcount:custcnt, ordercount:ordercnt, itemcount:itemcnt, time:(System.currentTimeMillis() - start)]
    }


    def itemsTableJSON(sql, db, page, pageSize) {

        def start = System.currentTimeMillis()
/*
            "SELECT it.item_id itemid,it.accwareCatID accwareid,it.cost_price costprice,it.sell_price sellprice,it.hot_price reslprice," +
            "it.stock_quantity qty,it.stock stock," +
            "gc.accwareCatID parentid,gc.name parentname,sc.category_id catid,sc.accwareCatID categoryid,sc.name catname," +
            "sc.searchName1 prefname1,sc.searchName2 prefname2,sc.searchName3 prefname3,sc.searchName4 prefname4,sc.searchName5 prefname5," +
            "iv.vendor_id vendid,iv.alias alias,iv.name vendname," +
            "it.name itemname,ig.itemname advname,it.mfr_name mfrname,it.mfr_number mfrnumber,ia.mfrName advmfrname,ia.mfrNumber advmfrnumber,it.adv_price advprice," +
            "it.mfr_link mfrlink,ia.keywords keywords,it.package_include packinclude,it.comments packcomment," +
            "CAST(it.stockDate AS CHAR) stockdate," +//IF(it.stockDate='0000-00-00','0000-00-00',it.stockDate)
            "it.weight weight,it.res_price1 reslprice1,it.res_price2 reslprice2,it.res_price3 reslprice3," +
            "it.res_quantity2 reslqty2,it.res_quantity3 reslqty3,it.drone drone," +
            "it.warrantyMask wtymask,it.warrantyPrice1 wtyprice1,it.warrantyPrice2 wtyprice2,it.warrantyPrice3 wtyprice3,im.lot lot,it.active active," +
            "ig.groups googlegroup," +
            "IFNULL(idc.xml,'') xml,IFNULL(idc.xmlsize,0) xmlsize," +
            "pr.pref1 pref1,pr.pref2 pref2,pr.pref3 pref3,pr.pref4 pref4,pr.pref5 pref5," +
            "ia.cnet cnet,ia.grabber grabber,ia.bizrate bizrate,ia.nexttag nextag,ia.shopping shopping,ia.powersource powersource,ia.froogle froogle," +
            "im.special special,im.closeout closeout,im.commingsoon comingsoon,im.bestprice bestprice,im.firstpage firstpage," +
            "im.categorypage categorypage,im.bannerpage ebay,ia.cj cj,ia.banner_search gadgetpage,ia.upc upccode " +
            "FROM sklad.items it " +
            "LEFT JOIN sklad.itemsDesc idc USING(item_id) " +
            "LEFT JOIN sklad.itemsOnGoogle ig USING(item_id) " +
            "LEFT JOIN sklad.itemsAdvert ia ON ia.item_id=it.item_id " +
            "LEFT JOIN sklad.itemsMask im ON im.item_id=it.item_id " +
            "LEFT JOIN sklad.preference pr ON pr.item_id=it.item_id " +
            "LEFT JOIN sklad.itemVendor iv ON iv.vendor_id=it.vendor_id " +
            "LEFT JOIN sklad.category sc ON sc.category_id=it.category_id " +
            "LEFT JOIN sklad.category gc ON sc.parent=gc.category_id " +
            "LIMIT 1"
*/
        sql.eachRow(
        """SELECT it.item_id itemId,it.accwareCatID aid,gc.accwareCatID pid,gc.name pnm,sc.accwareCatID cid,sc.name cnm,
        IF(it.active='Y',1,0) active
        FROM sklad.items it
        LEFT JOIN sklad.itemsDesc idc USING(item_id)
        LEFT JOIN sklad.itemsOnGoogle ig USING(item_id)
        LEFT JOIN sklad.itemsAdvert ia ON ia.item_id=it.item_id
        LEFT JOIN sklad.itemsMask im ON im.item_id=it.item_id
        LEFT JOIN sklad.preference pr ON pr.item_id=it.item_id
        LEFT JOIN sklad.itemVendor iv ON iv.vendor_id=it.vendor_id
        LEFT JOIN sklad.category sc ON sc.category_id=it.category_id
        LEFT JOIN sklad.category gc ON sc.parent=gc.category_id
        LIMIT 10"""
        ) { newDoc ->
            //println "doc=${newDoc.aid} a=${newDoc.active}"
            try {
                def oldDoc = db.categories.findOne(_id: newDoc.pid)
                //println "cur=${cur ? cur : 'not find'}"
                if(oldDoc) {
                    def sub = oldDoc.sub.find{ it._id == newDoc.cid}
                    //println "Have we such sub in category? ${sub ? 'Yes' : 'No'}"
                    if(sub) {
                        //println "sub find ${sub}"
                        def item = sub.itms.find{ it == newDoc.aid}
                        //println "Have we such item in itm? ${item}"
                        if(item) {
                            println "Find item ${item} but need check what changes"
                        }
                        else {
                            //println "add item and cnt + 1 item=${item}"
                            addNewItem(db, newDoc)
                        }
                    }
                    else {
                        //println "add new sub"
                        insertNewSubcatItem(db, newDoc, oldDoc)
                    }
                }
                else {
                    //println "insert new category sub and item"
                    insertNewCatSubcatItem(db, newDoc)
                }
            }
            catch(e) {
                println "itemid:${newDoc.itemId}, error:${e}"
            }
        } //

        //return [page:page, custcount:custcnt, ordercount:ordercnt, itemcount:itemcnt, time:(System.currentTimeMillis() - start)]
    }

    // insert new cat, subcat and item
    def insertNewCatSubcatItem = { db, doc ->
        db.categories.insert([_id: doc.pid, name: doc.pnm, sub:[[_id: doc.cid, name: doc.cnm, cnt: 1, itms:[doc.aid]]]])
        insertNewItem(db, doc)
    }

    // insert to exists cat new subcat and item
    def insertNewSubcatItem = { db, ndoc, odoc ->
        //println "ndoc=${ndoc.pnm}\nodoc=${odoc.name}"
        if(odoc.name != ndoc.pnm) {
            db.categories.update([_id: ndoc.pid], [name: ndoc.name])
        }
        db.categories.update([_id: ndoc.pid], [$push: ['sub': [_id: ndoc.cid, name: ndoc.cnm, cnt: 1, itms:[ndoc.aid]]]])
        insertNewItem(db, ndoc)
    }

    // add to exists cat and subcat new item
    def addNewItem = { db, doc ->
        db.categories.update([_id: doc.pid, 'sub._id': doc.cid], [$push: ['sub.$.itms': doc.aid]])
        db.categories.update([_id: doc.pid, 'sub._id': doc.cid], [$inc: ['sub.$.cnt': 1]])
        insertNewItem(db, doc)
    }

    // insert new item
    def insertNewItem = { db, doc ->
        db.items.insert([_id: doc.aid, name: 'Test items', act: 1])
    }

    // update item
    def updateItem = { db, doc ->
    }

    // delete item
    def deleteItem = { db, doc ->
    }


}
